package com.yunkeji.system.mapper;

import cn.hutool.core.date.DateTime;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;

public interface AnalysesMapper {

    /**
     * 充值统计
     *
     * @author 老张
     */
    @Select(
            "SELECT "
                    + " count( id ) sumNum, "
                    + " DATE_FORMAT( "
                    + "  CONCAT( date( create_time ), ' ', HOUR ( create_time ), ':', floor( MINUTE ( create_time ) / 30 )* 30, ':00' ), "
                    + "  '%H:%i'  "
                    + " ) time  "
                    + " FROM "
                    + " ykj_pay_detail  "
                    + " WHERE "
                    + " STATUS = 1 and create_time >= #{beginOfDay}"
                    + " GROUP BY "
                    + " time  "
                    + " ORDER BY "
                    + " time ")
    List<HashMap<String, Object>> rechargeLine(@Param("beginOfDay") DateTime beginOfDay);
    /**
     * 通话统计
     *
     * @author 老张
     */
    @Select(
            "SELECT "
                    + " count( id ) sumNum, "
                    + " DATE_FORMAT( "
                    + "  CONCAT( date( create_time ), ' ', HOUR ( create_time ), ':', floor( MINUTE ( create_time ) / 30 )* 30, ':00' ), "
                    + "  '%H:%i'  "
                    + " ) time  "
                    + " FROM "
                    + " ykj_call_sheet_info  "
                    + " WHERE "
                    + " STATUS in(6,7,8) and create_time >= #{beginOfDay}"
                    + " GROUP BY "
                    + " time  "
                    + " ORDER BY "
                    + " time ")
    List<HashMap<String, Object>> callLine(@Param("beginOfDay") DateTime beginOfDay);
    /**
     * 注册统计
     *
     * @author 老张
     */
    @Select(
            "SELECT "
                    + " count( id ) sumNum, "
                    + " DATE_FORMAT( "
                    + "  CONCAT( date( create_time ), ' ', HOUR ( create_time ), ':', floor( MINUTE ( create_time ) / 30 )* 30, ':00' ), "
                    + "  '%H:%i'  "
                    + " ) time  "
                    + " FROM "
                    + " ykj_app_user  "
                    + " WHERE "
                    + "  create_time >= #{beginOfDay}"
                    + " GROUP BY "
                    + " time  "
                    + " ORDER BY "
                    + " time ")
    List<HashMap<String, Object>> regLine(@Param("beginOfDay") DateTime beginOfDay);

    /**
     * 在线统计
     *
     * @author 老张
     */
    @Select(
            " SELECT  "
                    + "  IFNULL(count( 1 ),0) value ,  "
                    + "CASE  "
                    + "      "
                    + "    WHEN sex = 1 THEN  "
                    + "    '女'   "
                    + "    WHEN sex = 0 THEN  "
                    + "    '男' ELSE '未知'   "
                    + "  END AS name   "
                    + "FROM  "
                    + "  ykj_app_user   "
                    + "WHERE  "
                    + "  sex !=2   "
                    + "  AND online_status != 0   "
                    + "GROUP BY  "
                    + "  sex")
    List<HashMap<String, Object>> onlineUser();
    /**
     * 提现统计
     *
     * @author 老张
     */
    @Select(
            "SELECT IFNULL(sum(cny_price) ,0) sum from ykj_pay_withdraw where pay_status =2 and create_time >= #{beginOfDay}")
    BigDecimal withdraw(@Param("beginOfDay") DateTime beginOfDay);
    /**
     * 充值统计
     *
     * @author 老张
     */
    @Select(
            "SELECT IFNULL(sum(cny_price) ,0) sum from ykj_pay_detail where status =1 and create_time >= #{beginOfDay}")
    BigDecimal recharge(@Param("beginOfDay") DateTime beginOfDay);
    /**
     * 通话统计-失败
     *
     * @author 老张
     */
    @Select(
            " SELECT IFNULL(COUNT(1) ,0) sum from ykj_call_sheet_info WHERE status not in(6,7,8) and create_time >= #{beginOfDay}")
    Integer callFail(@Param("beginOfDay") DateTime beginOfDay);

    /**
     * 通话统计-成功
     *
     * @author 老张
     */
    @Select(
            " SELECT IFNULL(COUNT(1) ,0) sum from ykj_call_sheet_info WHERE status  in(6,7,8) and create_time >= #{beginOfDay}")
    Integer callSuccess(@Param("beginOfDay") DateTime beginOfDay);

    @Select(
            " SELECT IFNULL(sum(cny_price) ,0) from ykj_pay_detail WHERE status  =1 and create_time >= #{start} and create_time <= #{end}")
    BigDecimal sumRecharge(@Param("start") DateTime start, @Param("end") DateTime end);

    @Select(
            " SELECT IFNULL(sum(expected_num) ,0) from ykj_pay_withdraw WHERE pay_status  = 2 and check_status=2 and create_time >= #{start} and create_time <= #{end}")
    BigDecimal sumWithdraw(@Param("start") DateTime start, @Param("end") DateTime end);

    @Select(
            " SELECT IFNULL(sum(duration) ,0)  from ykj_call_sheet_info WHERE status  in(6,7,8) and create_time >= #{start} and create_time <= #{end}")
    BigDecimal sumCall(@Param("start") DateTime start, @Param("end") DateTime end);

    @Select(
            " SELECT IFNULL(count(1) ,0)  sum from ykj_app_user WHERE  create_time >= #{start} and create_time <= #{end}")
    Integer sumReg(@Param("start") DateTime start, @Param("end") DateTime end);
}
